import logging
from datetime import date
from typing import Optional

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db

equipment_inv_router = APIRouter(prefix="/equipment/equipmentInv", tags=["设备台账管理"])


class InvSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    fequipmenttag: str = Field(None, description="设备标签")
    fpointNo: str = Field(None, description="场地编号")
    fpointSize: str = Field(None, description="场地位置")
    fequipmenttype: str = Field(None, description="")
    fequipmentbarcode: str = Field(None, description="固定资产码")
    fmacno: str = Field(None, description="机器码/序列号")
    fstate: str = Field(None, description="设备状态")


# todo  7.设备台账
@equipment_inv_router.get("/list", summary="查询设备台账")
def list_equipment_inv(inv: InvSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_tc_equipment_inv a,s_ts_equipment_info b where a.feuipmentid=b.feuipmentid "
    if inv.fstate is not None:
        sql = sql + " and a.fstate = :fstate "
        param["fstate"] = inv.fstate
    if inv.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = inv.fsiteNo
    if inv.fequipmenttag is not None:
        sql = sql + " and a.fequipmenttag like concat('%',:fequipmenttag,'%')"
        param["fequipmenttag"] = inv.fequipmenttag
    if inv.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = inv.fpointNo
    if inv.fpointSize is not None:
        sql = sql + " and a.fpoint_size like concat('%',:fpointSize,'%')"
        param["fpointSize"] = inv.fpointSize
    if inv.fequipmenttype is not None:
        sql = sql + " and b.fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = inv.fequipmenttype
    if inv.fequipmentbarcode is not None:
        sql = sql + " and a.fequipmentbarcode like concat('%',:fequipmentbarcode,'%')"
        param["fequipmentbarcode"] = inv.fequipmentbarcode
    if inv.fmacno is not None:
        sql = sql + " and a.fmacno like concat('%',:fmacno,'%')"
        param["fmacno"] = inv.fmacno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fequipmentbarcode,a.feuipmentid,a.fmacno,a.fequipmenttag,a.fsite_no,a.fpoint_no,a.fpoint_size,"
    sql = sql + " a.flinkmac,a.flinkmark,a.fprocdate,a.fsupno,a.frecbillno,a.frecdate,a.finstalldate,a.fmaintaindate,"
    sql = sql + " a.frepairdate,a.flosdate,a.fbuyprice,a.foldway,a.foldmoney,a.flastmoney,a.flastrate,a.fversion,"
    sql = sql + " a.fmarkstat,fvideourl,a.fstate,a.fip,a.fport,a.fupddate,a.foperator,a.fmeeting,a.fsubtitle,a.fpubtitle,a.fpuborder"
    sql = sql + " from s_tc_equipment_inv a,s_ts_equipment_info b where a.feuipmentid=b.feuipmentid"
    if inv.fstate is not None:
        sql = sql + " and a.fstate = :fstate "
        param["fstate"] = inv.fstate
    if inv.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = inv.fsiteNo
    if inv.fequipmenttag is not None:
        sql = sql + " and a.fequipmenttag like concat('%',:fequipmenttag,'%')"
        param["fequipmenttag"] = inv.fequipmenttag
    if inv.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = inv.fpointNo
    if inv.fpointSize is not None:
        sql = sql + " and a.fpoint_size like concat('%',:fpointSize,'%')"
        param["fpointSize"] = inv.fpointSize
    if inv.fequipmenttype is not None:
        sql = sql + " and b.fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = inv.fequipmenttype
    if inv.fequipmentbarcode is not None:
        sql = sql + " and a.fequipmentbarcode like concat('%',:fequipmentbarcode,'%')"
        param["fequipmentbarcode"] = inv.fequipmentbarcode
    if inv.fmacno is not None:
        sql = sql + " and a.fmacno like concat('%',:fmacno,'%')"
        param["fmacno"] = inv.fmacno
    if inv.order == "desc":
        sql = sql + " order by  a.fsite_no,fpoint_no,a.feuipmentid  desc "
    else:
        sql = sql + " order by  a.fsite_no,fpoint_no,a.feuipmentid  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = inv.limit
    param["offset"] = inv.offset

    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fequipmentbarcode'] = data[key][0]
        d1['feuipmentid'] = data[key][1]
        d1['fmacno'] = data[key][2]
        d1['fequipmenttag'] = data[key][3]
        d1['fsiteNo'] = data[key][4]
        d1['fpointNo'] = data[key][5]
        d1['fpointSize'] = data[key][6]
        d1['flinkmac'] = data[key][7]
        d1['flinkmark'] = data[key][8]
        d1['fprocdate'] = data[key][9]
        d1['fsupno'] = data[key][10]
        d1['frecbillno'] = data[key][11]
        d1['frecdate'] = data[key][12]
        d1['finstalldate'] = data[key][13]
        d1['fmaintaindate'] = data[key][14]
        d1['frepairdate'] = data[key][15]
        d1['flosdate'] = data[key][16]
        d1['fbuyprice'] = data[key][17]
        d1['foldway'] = data[key][18]
        d1['foldmoney'] = data[key][19]
        d1['flastmoney'] = data[key][20]
        d1['flastrate'] = data[key][21]
        d1['fversion'] = data[key][22]
        d1['fmarkstat'] = data[key][23]
        d1['fvideourl'] = data[key][24]
        d1['fstate'] = data[key][25]
        d1['fip'] = data[key][26]
        d1['fport'] = data[key][27]
        d1['fupddate'] = data[key][28]
        d1['foperator'] = data[key][29]
        d1['fmeeting'] = data[key][30]
        d1['fsubtitle'] = data[key][31]
        d1['fpubtitle'] = data[key][32]
        d1['fpuborder'] = data[key][33]

        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": inv.limit,
                 "page": inv.pageIndex,
                 "pages": (count + inv.limit - 1) // inv.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class EquipmentInvCreate(BaseModel):
    """设备台账创建模型"""
    fequipmentbarcode: str = Field(..., description="设备条码")
    feuipmentid: int = Field(..., description="设备ID")
    fsiteNo: str = Field(..., description="站点编号")
    fstate: int = Field(..., description="设备状态（1:在用，0:停用）")
    foperator: str = Field(..., description="操作人")
    fupddate: date = Field(..., description="更新日期")

    # 基础信息
    fequipmenttag: str = Field(..., description="设备标签")
    fequipmenttype: str = Field(..., description="设备类型")
    fpointNo: str = Field("", description="点位编号")
    fpointSize: str = Field("", description="点位规格")
    fmacno: str = Field("", description="MAC地址")
    flinkmac: str = Field("", description="关联MAC地址")
    flinkmark: str = Field("", description="关联标记")
    fsupno: str = Field("", description="供应商编号")
    fip: str = Field("", description="IP地址")
    fport: str = Field("", description="端口号")
    fvideourl: str = Field("", description="视频URL")
    fmeeting: int = Field(0, description="会议标识")  # 0或1

    # 财务信息（字符串转数值，默认0）
    fbuyprice: float = Field(0.0, description="采购价格")
    foldway: float = Field(0.0, description="折旧方式")
    foldmoney: float = Field(0.0, description="折旧金额")
    flastmoney: float = Field(0.0, description="残值")
    flastrate: float = Field(0.0, description="残值率")

    # 日期字段（可选，支持ISO格式字符串）
    fprocdate: Optional[date] = Field(None, description="生产日期")
    finstalldate: Optional[date] = Field(None, description="安装日期")
    fmaintaindate: Optional[date] = Field(None, description="维护日期")
    frepairdate: Optional[date] = Field(None, description="维修日期")
    flosdate: Optional[date] = Field(None, description="报废日期")

    # 设备使用年限（默认1年）
    fuseyear: int = Field(1, description="使用年限", ge=1)


@equipment_inv_router.post("/create", summary="新增设备台账")
def create_equipment_inv(inv: EquipmentInvCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = (
                "insert into s_tc_equipment_inv(fequipmentbarcode,feuipmentid,fmacno,fequipmenttag,fsite_no,fpoint_no,fpoint_size,flinkmac,flinkmark,"
                "fsupno,fbuyprice,foldway,foldmoney,flastmoney,flastrate,fvideourl,fstate,fip,fport,fupddate,foperator,fmeeting")
            if inv.fprocdate is not None:
                sql = sql + ",fprocdate"
            if inv.finstalldate is not None:
                sql = sql + ",finstalldate"
            if inv.fmaintaindate is not None:
                sql = sql + ",fmaintaindate"
            if inv.frepairdate is not None:
                sql = sql + ",frepairdate"
            if inv.flosdate is not None:
                sql = sql + ",flosdate"
            sql = sql + ")"

            sql = sql + (
                " values( :fequipmentbarcode , :feuipmentid , :fmacno , :fequipmenttag , :fsiteNo , :fpointNo , :fpointSize , :flinkmac , :flinkmark , :fsupno ,"
                " :fbuyprice , :foldway , :foldmoney , :flastmoney , :flastrate , :fvideourl , :fstate , :fip , :fport , :fupddate , :foperator , :fmeeting ")
            if inv.fprocdate is not None:
                sql = sql + ",:fprocdate"
            if inv.finstalldate is not None:
                sql = sql + ", :finstalldate "
            if inv.fmaintaindate is not None:
                sql = sql + ", :fmaintaindate "
            if inv.frepairdate is not None:
                sql = sql + ", :frepairdate "
            if inv.flosdate is not None:
                sql = sql + ", :flosdate "
            sql = sql + ");"

            db.execute(text(sql), {
                "fequipmentbarcode": inv.fequipmentbarcode,
                "feuipmentid": inv.feuipmentid,
                "fmacno": inv.fmacno,
                "fequipmenttag": inv.fequipmenttag,
                "fsiteNo": inv.fsiteNo,
                "fpointNo": inv.fpointNo,
                "fpointSize": inv.fpointSize,
                "flinkmac": inv.flinkmac,
                "flinkmark": inv.flinkmark,
                "fsupno": inv.fsupno,
                "fbuyprice": inv.fbuyprice,
                "foldway": inv.foldway,
                "foldmoney": inv.foldmoney,
                "flastmoney": inv.flastmoney,
                "flastrate": inv.flastrate,
                "fvideourl": inv.fvideourl,
                "fstate": inv.fstate,
                "fip": inv.fip,
                "fport": inv.fport,
                "fupddate": inv.fupddate,
                "foperator": inv.foperator,
                "fmeeting": inv.fmeeting,
                "fprocdate": inv.fprocdate,
                "finstalldate": inv.finstalldate,
                "fmaintaindate": inv.fmaintaindate,
                "frepairdate": inv.frepairdate,
                "flosdate": inv.flosdate,
            })

            data = {"fbuyprice": inv.fbuyprice
                , "fequipmentbarcode": inv.fequipmentbarcode
                , "fequipmenttag": inv.fequipmenttag
                , "fequipmenttype": inv.fequipmenttype
                , "feuipmentid": inv.feuipmentid
                , "fimage": None
                , "finstalldate": inv.finstalldate
                , "fip": inv.fip
                , "flastmoney": inv.flastmoney
                , "flastrate": inv.flastrate
                , "flinkmac": inv.flinkmac
                , "flinkmark": inv.flinkmark
                , "flosdate": inv.flosdate
                , "fmacno": inv.fmacno
                , "fmaintaindate": inv.fmaintaindate
                , "fmarkstat": None
                , "fmeeting": 0
                , "foldmoney": inv.foldmoney
                , "foldway": inv.foldway
                , "foperator": inv.foperator
                , "fpointNo": inv.fpointNo
                , "fpointSize": inv.fpointSize
                , "fport": inv.fport
                , "fprocdate": None
                , "frecbillno": None
                , "frecdate": None
                , "frepairdate": inv.frepairdate
                , "fsiteNo": inv.fsiteNo
                , "fstate": inv.fstate
                , "fsupno": inv.fsupno
                , "fupddate": inv.fupddate
                , "fversion": None
                , "fvideourl": inv.fvideourl
                , "isUsed": None
                    }
            return BaseResponse.success(data=data)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@equipment_inv_router.post("/update", summary="修改设备台账")
def update_equipment_inv(inv: EquipmentInvCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():

            sql = "update s_tc_equipment_inv"
            sql = sql + " set fbuyprice= :fbuyprice ,fequipmenttag= :fequipmenttag "
            sql = sql + ",fip= :fip ,flastmoney= :flastmoney "
            sql = sql + ",flastrate= :flastrate ,flinkmac= :flinkmac ,flinkmark= :flinkmark "
            sql = sql + ",fmacno= :fmacno ,foldmoney= :foldmoney "
            sql = sql + ",foldway= :foldway ,fpoint_no= :fpointNo "
            sql = sql + ",fpoint_size= :fpointSize ,fport= :fport "
            sql = sql + ",fstate= :fstate ,fsupno= :fsupno ,fvideourl= :fvideourl "
            sql = sql + " ,fupddate= :fupddate ,foperator= :foperator ,fmeeting= :fmeeting "
            if inv.finstalldate is not None:
                sql = sql + ", finstalldate = :finstalldate "
            if inv.fprocdate is not None:
                sql = sql + ",fprocdate= :fprocdate "
            if inv.fmaintaindate is not None:
                sql = sql + ",fmaintaindate= :fmaintaindate "
            if inv.frepairdate is not None:
                sql = sql + ",frepairdate= :frepairdate "
            if inv.flosdate is not None:
                sql = sql + ",flosdate= :flosdate "
            sql = sql + " where fequipmentbarcode= :fequipmentbarcode "
            db.execute(text(sql), {
                "fequipmentbarcode": inv.fequipmentbarcode,
                "feuipmentid": inv.feuipmentid,
                "fmacno": inv.fmacno,
                "fequipmenttag": inv.fequipmenttag,
                "fsiteNo": inv.fsiteNo,
                "fpointNo": inv.fpointNo,
                "fpointSize": inv.fpointSize,
                "flinkmac": inv.flinkmac,
                "flinkmark": inv.flinkmark,
                "fsupno": inv.fsupno,
                "fbuyprice": inv.fbuyprice,
                "foldway": inv.foldway,
                "foldmoney": inv.foldmoney,
                "flastmoney": inv.flastmoney,
                "flastrate": inv.flastrate,
                "fvideourl": inv.fvideourl,
                "fstate": inv.fstate,
                "fip": inv.fip,
                "fport": inv.fport,
                "fupddate": inv.fupddate,
                "foperator": inv.foperator,
                "fmeeting": inv.fmeeting,
                "fprocdate": inv.fprocdate,
                "finstalldate": inv.finstalldate,
                "fmaintaindate": inv.fmaintaindate,
                "frepairdate": inv.frepairdate,
                "flosdate": inv.flosdate,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@equipment_inv_router.post("/delete/{fequipmentbarcode}", summary="删除设备台账")
def delete_equipment_inv(fequipmentbarcode: str = Path(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from s_tc_equipment_inv where fequipmentbarcode= :fequipmentbarcode "
            db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
